﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

using System.Data.Common;

namespace ShoppingApp
{
    public partial class SqlDataAdapterExample : System.Web.UI.Page
    {
        DataSet ds;
        SqlDataAdapter da;
        string str;
        
        protected string BuildConnectionString()
        {
            SqlConnectionStringBuilder con = new SqlConnectionStringBuilder();
            con.DataSource = @".\SQLEXPRESS";
            con.InitialCatalog = "ShoppingAppDb";
            con.UserID = "AspNetUser";
            con.Password = "password";
            
            return con.ConnectionString;
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (IsPostBack)
            {
                if (ViewState["str"] != null)
                {
                    str = ViewState["str"].ToString();
                }
                if (Session["adapter"] != null)
                {
                    da = (SqlDataAdapter)Session["adapter"];
                }
                if (Session["ds"] != null)
                {
                    ds = (DataSet)Session["ds"];
                }
                if (Session["da"] != null)
                {
                    da = (SqlDataAdapter)Session["da"];
                }
            }

        }

        /// <summary>
        /// Things that will be done with SqlDataAdapter
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void GetDataButton_Click(object sender, EventArgs e)
        {
            //Join Shops and Addresses in order to show user full data
            //string strSql = "SELECT s.Shop_Id, s.Name, s.ChainName, a.City, a.Street FROM dbo.Shops AS s " +
            //                "LEFT JOIN dbo.Addresses AS a " + 
            //                "ON s.Addr_Id = a.Addr_Id;";
            
            string strSql = "SELECT Addr_Id, City, Street FROM dbo.Addresses;";
            str = "This is string text";
            ViewState["str"] = str;
            da = null;

            using (SqlConnection cn = new SqlConnection(BuildConnectionString()))
            {
                try
                {
                    //Usefull with multiple SqlDataAdapters - do not have to open many connections
                    cn.Open();

                    //Constructor with connection object and SelectCommand property 
                    da = new SqlDataAdapter(strSql, cn);
                    Session.Add("adapter", da);

                    //Create new DataSet object to hold results
                    ds = new DataSet();
                    Session.Add("ds", ds);

                    //Optional return of SqlTypes
                    //da.ReturnProviderSpecificTypes = true;

                    //Executes query stored in the SqlDataAdapter - creates DataTable in DataSet
                    //da.MissingSchemaAction = MissingSchemaAction.AddWithKey;//this should also add primary key

                    //da.FillSchema(ds, SchemaType.Source);
                    da.Fill(ds);

                    if (ds.Tables[0].PrimaryKey != null)
                    {
                        foreach (DataColumn dc in ds.Tables[0].PrimaryKey)
                        {
                            System.Diagnostics.Debug.WriteLine("Checking column");
                        }
                    }
                    
                    
                    //Optional fetch results one more time - there should be duplicates
                    //ds.Tables[0].PrimaryKey = new DataColumn[] {ds.Tables[0].Columns[0]};
                    
                    //Primary key won't allow to add new data
                    //da.Fill(ds);
                    
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        //System.Diagnostics.Debug.WriteLine("{0}, {1}, {2}, {3}", row[0], row[1], row[2], row[3]);
                        System.Diagnostics.Debug.WriteLine("{0}, {1}, {2}", row[0], row[1], row[2]);
                    }

            
                    
                }
                catch (Exception ex)
                {
                    //Implement
                }

            }
        }

        //Submit data using SqlDataAdapter - primary keys are not used in this moment
        protected void SubmitButton_Click(object sender, EventArgs e)
        {
            //if (da != null)
            //{
            if (ds == null)
            {
                System.Diagnostics.Debug.WriteLine("DataSet object is null");
            }
            if (str == null)
            {
                System.Diagnostics.Debug.WriteLine("Str is null");
            }

                System.Diagnostics.Debug.WriteLine("After submit button clicked");
                //string query = "INSERT INTO dbo.Addresses (City, Street) VALUES (@City, @Street);"
                //    + "INSERT INTO dbo.Shops (Name, ChainName) VALUES (@Name, @ChainName);";

                string query = "INSERT INTO dbo.Addresses (City, Street) VALUES (@City, @Street);";
                //string query = "INSERT INTO dbo.Addresses (Addr_Id, City, Street) VALUES (@Addr_Id, @City, @Street);";

                //Add new row to existing DataSet
                DataRow row = ds.Tables[0].NewRow();//return a row with a schema from table
                row["City"] = CityTextBox.Text;
                row["Street"] = StreetTextBox.Text;
                
                //No information concerning primary key
                //row["Addr_Id"] = NameTextBox.Text;
                ds.Tables[0].Rows.Add(row);

                using (SqlConnection cn = new SqlConnection(BuildConnectionString()))
                {
                    //When we don't want to make update here there's no need to create connection
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    
                    cmd.Parameters.Add("@City", SqlDbType.VarChar, 30, "City");
                    cmd.Parameters.Add("@Street", SqlDbType.VarChar, 30, "Street");
                    //cmd.Parameters.Add("@Addr_Id", SqlDbType.Int, 2, "Addr_Id");
                    
                    da.InsertCommand = cmd;
                    da.Update(ds);
                }

                //Print information from dataset
                foreach (DataRow tempRow in ds.Tables[0].Rows)
                {
                    foreach (Object obj in tempRow.ItemArray)
                    {
                        System.Diagnostics.Debug.Write(obj.ToString()+"\t");
                    }
                    System.Diagnostics.Debug.WriteLine("");
                }
        }

        protected void GetDataSqlCommandButton_Click(object sender, EventArgs e)
        {
            //Get data using sql command
        }

        protected void InsertSqlCommandButton_Click(object sender, EventArgs e)
        {
            //Let's break a little our table
            string query = "INSERT INTO dbo.Addresses (City, Street) VALUES (@City, @Street);";
            string query2 = "INSERT INTO dbo.Addresses (City, Street) VALUES ('Warszawa', 'Bolka')";
            string query3 = "SELECT * FROM dbo.Addresses";

            using (SqlConnection cn = new SqlConnection(BuildConnectionString()))
            {
                try
                {
                    //Remember to open connection
                    cn.Open();

                    using (SqlCommand cmd = new SqlCommand(query, cn))
                    {
                        //At first using standard INSERT with parameters - insert into Addresses table 
                        SqlParameter p = new SqlParameter();
                        p.ParameterName = "@City";
                        p.Value = CityTextBox.Text;
                        cmd.Parameters.Add(p);//Add parameter to set of parameters
                       
                        //Another way to create a parameter
                        cmd.Parameters.AddWithValue("@Street", StreetTextBox.Text);

                        //Execute query 
                        try{
                            int rowsAffected = cmd.ExecuteNonQuery();
                                                       
                            if (rowsAffected != 0)
                            {
                                System.Diagnostics.Debug.WriteLine("Rows inserted: " + rowsAffected);
                            }
                            else
                            {
                                System.Diagnostics.Debug.WriteLine("No rows inserted!");
                            }
                        }
                        catch(Exception ex)
                        {
                            System.Diagnostics.Debug.WriteLine("Error executing query");
                            System.Diagnostics.Debug.WriteLine(ex.Message);
                        }
                    }
                }
                catch (Exception ex)
                {
                    //Implement
                }
            }
        }

        protected void InsertUsingStoredProcedureButton_Click(object sender, EventArgs e)
        {
            using (SqlConnection cn = new SqlConnection(BuildConnectionString()))
            {
                try{

                    cn.Open();
                                
                    using (SqlCommand cmd = new SqlCommand("sp_InsertShopProcedure", cn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@Name", NameTextBox.Text);
                        cmd.Parameters.AddWithValue("@City", CityTextBox.Text);
                        cmd.Parameters.AddWithValue("@Street", StreetTextBox.Text);

                        //Another way to supply parameters
                        cmd.Parameters.Add("@ChainName", SqlDbType.VarChar, 15);
                        cmd.Parameters["@ChainName"].SqlValue = ChainNameTextBox.Text;
                        
                        try
                        {
                            int rowsAffected = cmd.ExecuteNonQuery();

                            if (rowsAffected != 0)
                            {
                                System.Diagnostics.Debug.WriteLine("Rows inserted: " + rowsAffected.ToString());
                            }
                            else
                            {
                                System.Diagnostics.Debug.WriteLine("No rows inserted!");
                            }

                        }
                        catch (Exception ex)
                        {
                            System.Diagnostics.Debug.WriteLine(ex.Message);
                        }
                    }
                }
                catch(Exception ex)
                {
                    //Implement
                }
            }
        }

        protected void SynchronizeButton_Click(object sender, EventArgs e)
        {
            using(SqlConnection cn = new SqlConnection(BuildConnectionString()))
            {
                da.InsertCommand.Connection = cn;
                da.Update(ds);
            }
        }

        //Get With Schema Button
        protected void Button1_Click(object sender, EventArgs e)
        {
            //Get data using primary key at first
            string strSql = "SELECT Addr_Id, City, Street FROM dbo.Addresses";
            
            using (SqlConnection cn = new SqlConnection(BuildConnectionString()))
            {
                try
                {
                    da = new SqlDataAdapter(strSql, cn);
                    Session["da"] = da;

                    //Remove all data from DataSet if exists
                    ds = new DataSet();
                    
                    //da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    da.FillSchema(ds, SchemaType.Source);
                    da.Fill(ds);
                    //ds.Tables[0].Columns["Street"].AllowDBNull = false;
                    //Try to add primary key information after filling the table
                    //this also works - great!
                    //da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                    //Fill the same data set one more time
                    da.Fill(ds);
                    //ds.Tables[0].Constraints.Add("UK_Addreses", 
                    //This must be after the second fill
                    Session["ds"] = ds;

                    printDataSet(ds.Tables[0]);
                }
                catch (Exception ex)
                {
                    System.Diagnostics.Debug.WriteLine(ex.Message);
                }
                
                //Connection closes automatically
            }
        }

        /// <summary>
        /// Prints table from given DataSet to screen
        /// </summary>
        /// <param name="tableName">Name of table in DataSet</param>
        private void printDataSet(DataTable tableName)
        {
            foreach(DataRow row in tableName.Rows)
            {
                foreach (Object obj in row.ItemArray)
                {
                    System.Diagnostics.Debug.Write(obj.ToString() + ", ");
                }
                System.Diagnostics.Debug.WriteLine("");
            }

        }

        //Submit With Schema Button
        protected void Button2_Click(object sender, EventArgs e)
        {
            //string strSql = "INSERT INTO dbo.Addresses (City, Street) VALUES (@City, @Street)";
            string strSql = "INSERT INTO dbo.Addresses (Addr_Id, City, Street) VALUES (@Addr_Id, @City, @Street)";

            //At first try to insert into database duplicated rows
            using(SqlConnection cn = new SqlConnection(BuildConnectionString()))
            {
                try
                {
                    ds = (DataSet)Session["ds"];
                    da = (SqlDataAdapter)Session["da"];

                    SqlCommand insertCommand = new SqlCommand(strSql);
                    insertCommand.Parameters.Add("@City", SqlDbType.VarChar, 30, "City");
                    insertCommand.Parameters.Add("@Street", SqlDbType.VarChar, 30, "Street");
                    insertCommand.Parameters.Add("@Addr_Id", SqlDbType.Int, 2, "Addr_Id");

                    da.InsertCommand = insertCommand;
                    da.InsertCommand.Connection = cn;// no need to open connection earlier

                    DataRow row = ds.Tables[0].NewRow();
                    row["City"] = CityTextBox.Text;
                    if (StreetTextBox.Text != null)
                    {
                        System.Diagnostics.Debug.WriteLine("Not null in StreetBox");
                    }
                        
                    row["Street"] = StreetTextBox.Text;
                    row["Addr_Id"] = NameTextBox.Text;

                    ds.Tables[0].Rows.Add(row);
                    System.Diagnostics.Debug.WriteLine("Printing before adding");
                    printDataSet(ds.Tables[0]);

                 //   da.Update(ds);
                }
                catch (Exception ex)
                {
                    //this should be caught
                    System.Diagnostics.Debug.WriteLine(ex.Message);
                }
            }
            

        }

        
    }
}